/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package DBLayer;

import ModelLayer.Invoice;
import ModelLayer.InvoiceStatus;
import java.sql.*;

/**
 *
 * @author Henrik-iMac
 */
public class DBInvoiceStatus implements IFDBInvoiceStatus{
    private Connection con;

    public DBInvoiceStatus(){
        con = DbConnection.getInstance().getDBcon();
    }

    public InvoiceStatus findInvoiceStatusByInvoiceId(int invoiceId, boolean retrieveAssociation){
        String wClause = "invoiceId = "+ invoiceId;
        System.out.println("Invoice id: " + invoiceId);
        return singleWhere(wClause, retrieveAssociation);
    }

    public int insertInvoiceStatus(InvoiceStatus invoiceStatus){
        int rc = -1;

        String query ="INSERT INTO InvoiceStatus(invoiceId, status) VALUES("+
                invoiceStatus.getInvoiceId().getInvoiceNo()+ ", '" +
                invoiceStatus.getStatus() + "')";
        System.out.println("Insert: " + query);

        try {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            rc = stmt.executeUpdate(query);
            stmt.close();
        }
        catch (Exception e) {
            System.out.println("Insert exception in invoice db: "+e);
        }
        return rc;
    }

    public int updateInvoiceStatus(InvoiceStatus invoiceStatus){
        int rc = -1;
        InvoiceStatus invoiceStatusObj = invoiceStatus;

        String query ="UPDATE InvoiceStatus SET " +
                "invoiceId = " + invoiceStatusObj.getInvoiceId().getInvoiceNo() + ", " +
                "status = '" + invoiceStatusObj.getStatus() + "' " +
                "WHERE invoiceId= " + invoiceStatusObj.getInvoiceId().getInvoiceNo();
        System.out.println("Update query: "+ query);
        try {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            rc = stmt.executeUpdate(query);
            stmt.close();
        } catch (Exception e) {
            System.out.println("Update exception in invoicestatus db: " +e);
        }
        return rc;
    }
    //Singlewhere is used when we only select one supplier
    private InvoiceStatus singleWhere(String wClause, boolean retrieveAssociation)
    {
        ResultSet results;
        InvoiceStatus iObj = new InvoiceStatus();
        iObj.setInvoiceId(null);
        String query = buildQuery(wClause);
        System.out.println(query);
        try {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            if(results.next()){
                iObj = buildInvoiceStatus(results);
                stmt.close();
                if(retrieveAssociation)
                {
                    IFDBInvoice dbInvoice = new DBInvoice();
                    Invoice invoiceObj = dbInvoice.findInvoiceById(iObj.getInvoiceId().getInvoiceNo());
                    System.out.println("Zip code is selected");
                    iObj.setInvoiceId(invoiceObj);
                }
                else
                {
                    iObj.setInvoiceId(null);
                }
            }
        }
        catch (Exception e) {
            System.out.println("Query exception: "+e);
        }
        return iObj;
    }

    //method to build the query
    private String buildQuery(String wClause)
    {
        String query="SELECT * FROM InvoiceStatus";
        if (wClause.length()>0){
            query=query+" WHERE "+ wClause;
        }
        return query;
    }

    private InvoiceStatus buildInvoiceStatus(ResultSet results){
        InvoiceStatus iObj = new InvoiceStatus();
        try {
            iObj.setInvoiceId(new Invoice(results.getInt(1)));
            iObj.setStatus(results.getString(2));
        }
        catch (Exception e) {
            System.out.println("error in building the supplier object");
        }
        return iObj;
    }
}
